<?php
	include('../../configs/connect.php');
	include('../db.php');
	$db = new db_mysql();
	$db->connect($host,$name,$pass,$database);
	if(isset($_GET['cat'])){
		$cat = $_GET['cat'];
		$donvi = $_GET['donvi'];
		if(isset($_GET['nganh'])){
			$nganh = $_GET['nganh'];
		}
		if(isset($_GET['hocvu'])){
			$hocvu = $_GET['hocvu'];
			}
		if($cat=="mon"){
			$sql = "SELECT DISTINCT mh.ten_mh, mh.madm_mh from dm_monhoc AS mh
					INNER JOIN tbl_mon_ctdt AS mctdt ON mctdt.ma_monhoc = mh.madm_mh
					INNER JOIN tbl_lopmon_monctdt AS lmctdt ON lmctdt.ma_mon_ctdt = mctdt.ma_mon_ctdt
					INNER JOIN tbl_lopmon AS lm ON lm.ma_lopmon = lmctdt.ma_lopmon
					INNER JOIN tbl_ctdt AS ctdt ON mctdt.ma_ctdt = ctdt.ma_ctdt
					WHERE lm.ma_donvi = '$donvi' AND lm.ma_donvihocvu = '$hocvu' AND ctdt.madm_nganh = $nganh";
			$marr = $db->getList($sql);
				echo "<option value=\"\">Chọn môn thi</option>";
			foreach($marr as $mon){
				echo "<option value=\"".$mon['madm_mh']."\">".$mon['ten_mh']."</option>";
			}
		}
		else if($cat="nganh"){
		$sql="SELECT ctdt.madm_nganh, dmn.ten_nganh FROM tbl_ctdt AS ctdt
				INNER JOIN tbl_donvi_ctdt AS dvctdt ON dvctdt.ma_ctdt = ctdt.ma_ctdt
				INNER JOIN dm_nganh AS dmn ON dmn.madm_nganh = ctdt.madm_nganh
				WHERE dvctdt.ma_donvi = '$donvi'";
		
		$narr = $db->getList($sql);

			echo "<option value=\"\">Chọn ngành</option>";

			foreach($narr as $nganh){
			echo "<option value=\"".$nganh['madm_nganh']."\">".$nganh['ten_nganh']."</option>";
			}
		}
	}
	else if(isset($_GET['action'])){
		if($_GET['action']=="themds"){
			$mahoidong = $_POST['mahoidong'];
			$ma_mon_ctdt = $_POST['ma_mon_ctdt'];
			$cathi = $_POST['cathi'];
			$ngaythi = $_POST['ngaythi'];

			$svadd = preg_split("/,/",$_POST['push']);
			$svdel = preg_split("/,/",$_POST['pop']);
			$madsthi = $mahoidong.$ma_mon_ctdt.date('dmyhis');
			$sql = "INSERT INTO tbl_dsthi(ma_dsthi, ngaythi, ma_hoidongthi, madm_ca, ma_mon_ctdt,trangthai_dsthi) 
					VALUES ('$madsthi', '$ngaythi', '$mahoidong', '$cathi', '$ma_mon_ctdt','chuaco')";
			$db->query($sql);
			
            $madau_diemthi=$madsthi.date('dmyhis');
            $i=1;
			foreach($svadd as $mdkm){
			    $ma_diemthi=$madau_diemthi.$i;
				$db->query("INSERT INTO tbl_diemthi(ma_diemthi, ma_dkm, ma_dsthi, lanthi) VALUES ('$ma_diemthi', '$mdkm', '$madsthi', 1)");
                $i++;
			}
			foreach($svdel as $mdkm){
				$db->query("DELETE FROM tbl_diemthi WHERE ma_dkm='$mdkm' AND ma_dsthi = '$madsthi'");
			}
			header('Location: ../index.php?muc=tochucthi&modul=lapdsthi_filter');
		}
		else if($_GET['action']=="suads"){
			$madsthi = $_POST['madsthi'];
			$cathi = $_POST['cathi'];
			$ngaythi = $_POST['ngaythi'];

			if(!empty($_POST['push']))
			$svadd = preg_split("/,/",$_POST['push']);
			if(!empty($_POST['pop']))
			$svdel = preg_split("/,/",$_POST['pop']);

			$sql = "UPDATE tbl_dsthi SET ngaythi = '$ngaythi', madm_ca = '$cathi' 
					WHERE ma_dsthi = '$madsthi'";
			$db->query($sql);
			
			if(isset($svadd)){
			foreach($svadd as $mdkm){

				$db->query("INSERT INTO tbl_diemthi(ma_diemthi, ma_dkm, ma_dsthi, lanthi) VALUES ('$mdkm', '$mdkm', '$madsthi', 1)");
			}}
			if(isset($svdel)){
			foreach($svdel as $mdkm){
				$db->delete("tbl_diemthi", "ma_dkm='$mdkm' AND ma_dsthi = '$madsthi'");
			}}
			header('Location: ../index.php?muc=tochucthi&modul=suadsthi&dsthi='.$madsthi);
		}
		else if($_GET['action']=="dsbyhoidong"){
			$madonvi = $_GET['donvi'];
			$mahocvu = $_GET['hocvu'];
			$mahoidong = $db->getOneField("SELECT ma_hoidong FROM tbl_hoidongthi WHERE ma_donvihocvu = '$mahocvu' AND ma_donvi = '$madonvi'");
			$sql = "SELECT dst.ma_dsthi, dmn.ten_nganh, mh.ten_mh, dst.ngaythi, ch.khungthoigian FROM tbl_dsthi AS dst
										INNER JOIN dm_cahoc AS ch ON ch.madm_ca = dst.madm_ca
										INNER JOIN tbl_mon_ctdt AS mctdt ON dst.ma_mon_ctdt = mctdt.ma_mon_ctdt
										INNER JOIN dm_monhoc AS mh ON mh.madm_mh = mctdt.ma_monhoc
										INNER JOIN tbl_ctdt AS ctdt ON ctdt.ma_ctdt = mctdt.ma_ctdt
										INNER JOIN dm_nganh AS dmn ON dmn.madm_nganh = ctdt.madm_nganh
										WHERE dst.ma_hoidongthi = '$mahoidong'";
			if(isset($_GET['mon'])){
				$mon = $_GET['mon'];
				$sql .= " AND mh.madm_mh = '$mon'";
			}
			$listdsthi = $db->getList($sql);
			echo "	<table><tbody>
			<tr class='alt-row'>
				<th><b>Mã DS Thi</b></th>
				<th><b>Ngành</b></th>
				<th><b>Môn thi</b></th>
				<th><b>Ngày thi</b></th>
				<th><b>Ca thi</b></th>
				<th colspan='2'><b>Chức năng</b></th>
			</tr>
			";
			foreach($listdsthi as $dsthi){
				echo "<tr>
				<td>".$dsthi['ma_dsthi']."</td>
				<td>".$dsthi['ten_nganh']."</td>
				<td>".$dsthi['ten_mh']."</td>
				<td>".$dsthi['ngaythi']."</td>
				<td>".$dsthi['khungthoigian']."</td>
				<td><a href='index.php?muc=tochucthi&modul=suadsthi&dsthi=".$dsthi['ma_dsthi']."'><img title='Sửa' width='20p' height='20px' src='../php/images/edit.png'></a></td>
				<td><a href='#' class='delds' data-url='".$dsthi['ma_dsthi']."'><img title='Xóa' width='20p' height='20px' src='../php/images/delete.png'></a></td>
				</tr>";
			}
			echo "</tbody></table>";
		}
		else if($_GET['action']=="xoads"){
			$mads = $_GET['mads'];
			$db->delete('tbl_dsthi', "ma_dsthi = '$mads'");
			if($db->affected()){
				echo 1;
			} else echo 0;
		}
        else if($_GET['action']=="filter_mon"){
			$donvi = $_GET['donvi'];
			$nganh = $_GET['nganh'];
			$hocvu = $_GET['hocvu'];
			$mon = $_GET['mon'];
			$dataArr = array();
			//$nganhArr = array();
			//$monArr = array();
			if(!empty($donvi) && !empty($nganh)){
					$sql = "SELECT DISTINCT mh.ten_mh, mh.madm_mh from dm_monhoc AS mh
					INNER JOIN tbl_mon_ctdt AS mctdt ON mctdt.ma_monhoc = mh.madm_mh
					INNER JOIN tbl_lopmon_monctdt AS lmctdt ON lmctdt.ma_mon_ctdt = mctdt.ma_mon_ctdt
					INNER JOIN tbl_lopmon AS lm ON lm.ma_lopmon = lmctdt.ma_lopmon
					INNER JOIN tbl_ctdt AS ctdt ON mctdt.ma_ctdt = ctdt.ma_ctdt
					WHERE lm.ma_donvi = '$donvi' AND lm.ma_donvihocvu = '$hocvu' AND ctdt.madm_nganh = $nganh";
					$marr = $db->getList($sql);
					$dataArr['mon'] = $marr;
				}
                echo(json_encode($dataArr));
			}
			
			//print_r($dataArr);
			
		
		else if($_GET['action']=="filter_nganh"){
			$donvi = $_GET['donvi'];
			$nganh = $_GET['nganh'];
			$hocvu = $_GET['hocvu'];
			$mon = $_GET['mon'];
			$dataArr = array();
			//$nganhArr = array();
			//$monArr = array();
			if(!empty($donvi)){
				$sql="SELECT ctdt.madm_nganh, dmn.ten_nganh FROM tbl_ctdt AS ctdt
				INNER JOIN tbl_donvi_ctdt AS dvctdt ON dvctdt.ma_ctdt = ctdt.ma_ctdt
				INNER JOIN dm_nganh AS dmn ON dmn.madm_nganh = ctdt.madm_nganh
				WHERE dvctdt.ma_donvi = '$donvi'";
		
				$narr = $db->getList($sql);
				$dataArr['nganh'] = $narr;	

				
			}
			
			//print_r($dataArr);
			echo(json_encode($dataArr));
		}
		else if($_GET['action']=='datefilter'){
			if(isset($_GET['data'])){
			$dataArr = preg_split('[,]', $_GET['data']);
			
			$time=$dataArr[0];
			$cond = "";
			if($time != 00){
				 $cond = "AND lm.ngaybatdau = '$time'";
			}
			$mahdthi = $dataArr[1];
			$madonvi = $dataArr[3];
			$hocvu = $dataArr[5];
			$ma_mon_ctdt = $dataArr[2];
			$mamonhoc = $dataArr[4];
			$sql = "SELECT dkm.ma_sv, dkm.ma_dkm, sv.hoten_sv, sv.ngaysinh_sv, dkm.ma_lopmon FROM tbl_dangkymon AS dkm
					INNER JOIN tbl_sinhvien AS sv ON sv.ma_sv = dkm.ma_sv
					WHERE ma_lopmon IN(SELECT lm.ma_lopmon FROM tbl_lopmon AS lm
					INNER JOIN tbl_lopmon_monctdt AS lmctdt ON lm.ma_lopmon = lmctdt.ma_lopmon
					INNER JOIN  tbl_mon_ctdt AS mctdt ON lmctdt.ma_mon_ctdt = mctdt.ma_mon_ctdt
					INNER JOIN dm_monhoc AS mh ON mctdt.ma_monhoc = mh.madm_mh
					WHERE lm.ma_donvi = '$madonvi' AND lm.ma_donvihocvu = '$hocvu'
					AND mh.madm_mh = '$mamonhoc'$cond) AND dkm.ma_dkm NOT IN(SELECT ma_dkm FROM tbl_diemthi WHERE ma_dsthi IN(SELECT DISTINCT ma_dsthi FROM `tbl_dsthi` WHERE ma_hoidongthi = '$mahdthi' AND ma_mon_ctdt = '$ma_mon_ctdt') ) ORDER BY sv.ten_sv ASC, sv.hoten_sv";
			$svarr = $db->getList($sql);
			
			echo '<tr class="alt-row">
				<th><input type="checkbox" value="" class="select-all" /></th>				
				<th><b>Mã SV</b></th>
				<th><b>Tên SV</b></th>
				<th><b>Ngày sinh</b></th>
				<th><b>Lớp môn</b></th>
				</tr>';
			foreach($svarr AS $sv){
			echo '<tr>
					<td><input type="checkbox" value="'.$sv['ma_dkm'].'" class="sv-item" /></td>
					<td>'.$sv['ma_sv'].'</td>
					<td>'.$sv['hoten_sv'].'</td>
					<td>'.$sv['ngaysinh_sv'].'</td>
					<td>'.$sv['ma_lopmon'].'</td>
				</tr>';
			}
			}
		}
        else if($_GET['action']=='getDateHd'){
            $madonvi = $_GET['donvi'];
            $mahocvu = $_GET['hocvu'];
            $sql = "SELECT ma_hoidong, thoigian_hoidong FROM tbl_hoidongthi WHERE ma_donvi = '$madonvi' AND ma_donvihocvu = '$mahocvu'";
           $listdate = $db->getList($sql);
           foreach($listdate AS $date){
                echo '<option value="'.$date['ma_hoidong'].'">'.$date['thoigian_hoidong'].'</option>';
           }
           
        }
		else if($_GET['action']== 'locdanhsach'){
			
			$mahocvu = $_GET['hocvu'];
            $mahoidong = $_GET['hoidong'];
            $madonvi = $_GET['donvi'];
            $manganh = $_GET['nganh'];
            $mamon = $_GET['mon'];	
            $type = $_GET['type'];
			//$mahoidong = 1;
			
			$cond = "hd.ma_donvihocvu = '$mahocvu'";
            if(!empty($mahoidong)){
                $cond = "hd.ma_hoidong = '$mahoidong'";
            } else if(!empty($madonvi)){
                $cond .= " AND dv.ma_donvi = '$madonvi'";
            }
			if(!empty($manganh)) {	
				$cond .= " AND dmn.madm_nganh = '$manganh'";
			}
			if(!empty($mamon)) {
				$cond .= " AND mctdt.ma_monhoc = '$mamon'";
			}
			$sql = "SELECT dst.ma_dsthi, dv.ten_donvi, dmn.ten_nganh, mh.ten_mh, dst.ngaythi, ch.khungthoigian, ttdt.ten_tt_diemthi,ttdt.madm_tt_diemthi
                                         FROM tbl_dsthi AS dst
										INNER JOIN dm_cahoc AS ch ON ch.madm_ca = dst.madm_ca
										INNER JOIN tbl_mon_ctdt AS mctdt ON dst.ma_mon_ctdt = mctdt.ma_mon_ctdt
										INNER JOIN dm_monhoc AS mh ON mh.madm_mh = mctdt.ma_monhoc
										INNER JOIN tbl_ctdt AS ctdt ON ctdt.ma_ctdt = mctdt.ma_ctdt
										INNER JOIN dm_nganh AS dmn ON dmn.madm_nganh = ctdt.madm_nganh
										INNER JOIN tbl_hoidongthi AS hd on hd.ma_hoidong = dst.ma_hoidongthi
										INNER JOIN tbl_donvi AS dv ON hd.ma_donvi = dv.ma_donvi
                                        INNER JOIN dm_trangthai_diemthi AS ttdt ON ttdt.madm_tt_diemthi=dst.trangthai_dsthi
										WHERE $cond";
			
			
			
			$listdsthi = $db->getList($sql);
            
			echo $sql.'<tr>
				<th class="cangiua">Mã DS thi</th>
				<th class="cangiua td-230">Hội đồng thi</th>
				<th class="cangiua td-150">Môn thi</th>
				<th class="cangiua td-80">Ngày thi</th>
				<th class="cangiua td-80">Ca thi</th>
				<th class="cangiua td-100">Trạng thái</th>
				<th class="cangiua td-80">Tác vụ</th>				
				</tr>';
			foreach($listdsthi as $dsthi){
				echo '
				<tr>
				<td class="cangiua">'.$dsthi['ma_dsthi'].'</td>
				<td class="cangiua">'.$dsthi['ten_donvi'].'</td>
				<td class="cangiua">'.$dsthi['ten_mh'].'</td>
				<td class="cangiua">'.$dsthi['ngaythi'].'</td>
				<td class="cangiua">'.$dsthi['khungthoigian'].'</td>
                <td class="cangiua">'.$dsthi['ten_tt_diemthi'].'</td>
				<td class="canphai">
                ';                    
                if ($dsthi['madm_tt_diemthi']=='chuaco' || $dsthi['madm_tt_diemthi']=='danhap'){
                    echo '<a href="index.php?muc=diem&modul=add_diemthi&ds_thi='.$dsthi['ma_dsthi'].'"><img title="Nhập điểm" src="../php/images/8.png" width="25px"/></a>&nbsp;';
                }  
                if($type == 'dsdiem') {
                    echo '<a href="index.php?muc=diem&modul=xacnhandiem&ds_thi='.$dsthi['ma_dsthi'].'"><img title="Xác nhận điểm" src="../php/images/2.png" width="25px"/></a>&nbsp;';
                }else{
                    echo '<a href="index.php?muc=tochucthi&modul=suadsthi&dsthi='.$dsthi['ma_dsthi'].'"><img src="../php/images/edit.png" width="20px" title="Sửa" /></a>&nbsp;';
                    }
                if($type == 'lapdsthi') {
                    echo '<a href="#" onclick="xoadsthi('.$dsthi['ma_dsthi'].')"><img title="Xóa danh sách" class="delds" data-url="'.$dsthi['ma_dsthi'].'" src="../php/images/del.png" width="20px"></a>';
                }
                '</td>
				</tr>';
			}
		}
	}

?>